package bysj.dao;


import bysj.domain.GraduateProjectStatus;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class GraduateProjectStatusDao {
    private static GraduateProjectStatusDao graduateProjectStatusDao = new GraduateProjectStatusDao();

    /*static {
        graduateProjectStatuses = new TreeSet<GraduateProjectStatus>();
        GraduateProjectStatus graduateProjectStatus = new GraduateProjectStatus(1, "申请", "01", "");
        graduateProjectStatuses.add(graduateProjectStatus);
        graduateProjectStatuses.add(new GraduateProjectStatus(2, "通过", "02", ""));
        graduateProjectStatuses.add(new GraduateProjectStatus(3, "退修", "03", ""));
    }*/

    private GraduateProjectStatusDao() {
    }

    public static GraduateProjectStatusDao getInstance() {
        return graduateProjectStatusDao;
    }

    public Collection<GraduateProjectStatus> finaAll(Connection connection, List<Condition> conditionList, Pagination pagination)throws SQLException {
        ///创建集合类对象，用来保存所有记录代表的Department对象
        Collection<GraduateProjectStatus> graduateProjectStatuses = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM graduateProjectStatuses");
        int totalNum = CommonDao.getInstance().count(connection,"graduateProjectStatuses");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            //System.out.println(sql_select);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            GraduateProjectStatus graduateProjectStatus = null;
            int id= resultSet.getInt(1);
            //为graduateProjectType对象的description赋值
            String description = resultSet.getString(2);
            //为graduateProjectType对象的no赋值
            String no = resultSet.getString(3);
            //为graduateProjectType对象的remarks赋值
            String remarks = resultSet.getString(4);
            //将graduateProjectType增加到graduateProjectTypes集合
            graduateProjectStatus = new GraduateProjectStatus(id,description,no,remarks);
            graduateProjectStatuses.add(graduateProjectStatus);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        return graduateProjectStatuses;
    }

    public GraduateProjectStatus find(Integer id,Connection connection) throws SQLException {
        //创建GraduateProjectType对象
        GraduateProjectStatus graduateProjectStatus = new GraduateProjectStatus(id,"description", "no", "remarks");
        //创建sql语句
        String graduateProjectType_sql = "SELECT * FROM graduateProjectStatus WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(graduateProjectType_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()){
            //为graduateProjectType对象的description赋值
            graduateProjectStatus.setDescription(resultSet.getString(2));
            //为graduateProjectType对象的no赋值
            graduateProjectStatus.setNo(resultSet.getString(3));
            //为graduateProjectType对象的remarks赋值
            graduateProjectStatus.setRemarks(resultSet.getString(4));
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return graduateProjectStatus;
    }

    public boolean update(GraduateProjectStatus graduateProjectStatus,Connection connection) throws SQLException {
        //创建sql语句
        String graduateProjectType_sql = "update graduateProjectStatus set no=?,description=?,remarks=? where id="+graduateProjectStatus.getId();
        //在该连接创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(graduateProjectType_sql);
        //为预编译参数赋值
        preparedStatement.setString(1,graduateProjectStatus.getNo());
        preparedStatement.setString(2,graduateProjectStatus.getDescription());
        preparedStatement.setString(3,graduateProjectStatus.getRemarks());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        //如果修改成功返回true，失败返回false
        if (affectedRowNum == 0) {
            return false;
        } else {
            return true;
        }
    }


    public Integer add(GraduateProjectStatus graduateProjectStatus,Connection connection) throws SQLException {
        //创建sql语句
        String graduateProjectType_sql = "INSERT INTO graduateProjectStatus(description,no,remarks) Values"+"(?,?,?)";
        //获得id
        int id = graduateProjectStatus.getId();
        //在该连接创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(graduateProjectType_sql);
        //为预编译参数赋值
        preparedStatement.setString(1,graduateProjectStatus.getDescription());
        preparedStatement.setString(2,graduateProjectStatus.getNo());
        preparedStatement.setString(3,graduateProjectStatus.getRemarks());
        //执行预编译对象的executeUpdate方法，获得添加的记录行数
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,connection);
        //如果添加失败 返回null，添加成功，返回school对象的id
        if(affectedRowNum==0){
            return null;
        }else {
            return id;
        }
    }

    public boolean delete(Integer id, Connection connection) throws SQLException {
        //创建sql语句
        String school_sql = "delete from graduateProjectStatus where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(school_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        //isDelete的初始值为true
        boolean isDelete = true;
        //如果删除的记录的行数为0，那么isDelete为false
        if (affectedRowNum == 0) {
            isDelete = false;
        }
        return isDelete;
    }
}
